CREATE PROCEDURE [dbo].[AddIHIOPrescription]
    @PrescriptionId BIGINT,
    @WarehouseCode VARCHAR(3),
    @TrackingCode INT,
    @SequenceNumber SMALLINT
AS
BEGIN
    DECLARE @RowIndex INT;
    DECLARE @GoodsCode VARCHAR(15);
    DECLARE @DeliverCount INT;
    DECLARE @DeliverCountCache INT;
    DECLARE @Differ MONEY;
    DECLARE @Status TINYINT;
    DECLARE @CoveredCount INT;
    DECLARE @InsurerPercent REAL;
    DECLARE @InsurerAmount MONEY;
    DECLARE @TotalInsuredAmount MONEY;
    DECLARE @TotalInsurerAmount MONEY;
    DECLARE @InsurerAmountCache MONEY;
    DECLARE @BasePrice MONEY;
    DECLARE @CtrMojodi CHAR(1);
    DECLARE @Description NVARCHAR(MAX);
    DECLARE @ConsumptionCode VARCHAR(20);
    DECLARE @ConsumptionInstructionCode VARCHAR(20);
    DECLARE @Typeofuse NVARCHAR(MAX);
    DECLARE @MedicalGradeCode VARCHAR(3);
    DECLARE @MedicalGradeName NVARCHAR(45);
    DECLARE @MedicalGradeStatus NVARCHAR(10);
    DECLARE @DetailId UNIQUEIDENTIFIER;
    DECLARE @IsLock TINYINT;
    DECLARE @InsurerCode VARCHAR(5);
    DECLARE @PurchasePrice MONEY;
    DECLARE @IsBulk BIT = 0;
    DECLARE @Mobile VARCHAR(15);
    DECLARE @NationalCode VARCHAR(12);
    DECLARE @ExchangeAmount MONEY = 0;
    DECLARE @NextReferDay TINYINT = 0;
    DECLARE @CacheData AS TABLE
    (
        Id UNIQUEIDENTIFIER,
        RowIndex INT,
        IsBulk BIT,
        InsurerCode VARCHAR(3),
        InsurerName NVARCHAR(50),
        InsuredName NVARCHAR(50),
        InsuredLastName NVARCHAR(50),
        GenderCode TINYINT,
        BirthDate VARCHAR(10),
        Mobile VARCHAR(15),
        NationalCode VARCHAR(12),
        Medicalid INT,
        DoctorName NVARCHAR(100),
        DoctorLastName NVARCHAR(100),
        MedicalGradeCode VARCHAR(3),
        MedicalGradeName NVARCHAR(45),
        MedicalGradeStatus NVARCHAR(10),
        AccountValidTo VARCHAR(10),
        VisitDate VARCHAR(10),
        GoodsCode VARCHAR(15),
        DeliverCount INT,
        Price MONEY,
        Differ MONEY,
        Status TINYINT,
        CoveredCount INT,
        InsurerPercent REAL,
        InsurerAmount MONEY,
        TotalInsuredAmount MONEY,
        TotalInsurerAmount MONEY,
        BasePrice MONEY,
        Description NVARCHAR(MAX),
        ConsumptionCode VARCHAR(20),
        ConsumptionInstructionCode VARCHAR(20),
        Typeofuse NVARCHAR(MAX),
        Price_Kharid MONEY,
        IsPaper SMALLINT,
        TurnNo SMALLINT,
        OrderId BIGINT,
        ExchangeAmount MONEY,
        NextReferDay TINYINT
    );
    BEGIN TRY
        BEGIN TRAN;
        SET @DetailId =
        (
            SELECT TOP 1
                   Id
            FROM dbo.IHIOPrescription WITH (NOLOCK)
            WHERE TrackingCode = @TrackingCode
                  AND SequenceNumber = CASE
                                           WHEN @SequenceNumber = 0 THEN
                                               SequenceNumber
                                           ELSE
                                               @SequenceNumber
                                       END
            ORDER BY CreatedOn DESC
        );
        DELETE FROM @CacheData;
        INSERT INTO @CacheData
        (
            Id,
            RowIndex,
            IsBulk,
            InsurerCode,
            InsurerName,
            InsuredName,
            InsuredLastName,
            GenderCode,
            BirthDate,
            Mobile,
            NationalCode,
            Medicalid,
            DoctorName,
            DoctorLastName,
            AccountValidTo,
            VisitDate,
            Description,
            GoodsCode,
            DeliverCount,
            CoveredCount,
            InsurerPercent,
            InsurerAmount,
            TotalInsuredAmount,
            TotalInsurerAmount,
            BasePrice,
            ConsumptionCode,
            ConsumptionInstructionCode,
            Typeofuse,
            Price_Kharid,
            IsPaper,
            TurnNo,
            OrderId,
            ExchangeAmount,
            NextReferDay
        )
        SELECT IHIOPrescription.Id,
               RowIndex,
               dbo.IHIOPrescriptionDetail.IsBulk,
               InsurerCode,
               Sa_Name InsurerName,
               InsuredName,
               InsuredLastName,
               CASE
                   WHEN GenderCode IN ( 0, 2 ) THEN
                       1
                   ELSE
                       2
               END AS GenderCode,
               dbo.MiladiToShamsi(BirthDate) BirthDate,
               Mobile,
               NationalCode,
               Medicalid,
               DoctorName,
               DoctorLastName,
               dbo.MiladiToShamsi(AccountValidTo) AccountValidTo,
               dbo.MiladiToShamsi(VisitDate) VisitDate,
               Description,
               GoodsCode,
               DeliverCount,
               ISNULL(CoveredCount, 0) CoveredCount,
               CAST(ROUND(InsurerPercent, 6) AS REAL),
               InsurerAmount,
               IHIOPrescriptionDetail.TotalInsuredAmount,
               IHIOPrescriptionDetail.TotalInsurerAmount,
               BasePrice,
               ConsumptionCode,
               ConsumptionInstructionCode,
               CASE
                   WHEN ShapeCode IN ( 0, 1, 2 ) THEN
                       MT.PersianMeaning + CAST(MT4.PersianMeaning AS VARCHAR(50)) + N''
                   WHEN ShapeCode IN ( 6, 3 ) THEN
                       MT.PersianMeaning + CAST(NumberOfPeriod AS VARCHAR(50)) + N''
                   WHEN ShapeCode = 8 THEN
                       MT.PersianMeaning + CAST(NumberOfPeriod AS VARCHAR(50)) + N''
                   WHEN ShapeCode = 9 THEN
                       MT.PersianMeaning + CAST(NumberOfPeriod AS VARCHAR(50)) + N''
                   WHEN ShapeCode = 5 THEN
                       MT.PersianMeaning + CAST(MT3.PersianMeaning AS VARCHAR(50)) + N''
                   WHEN ShapeCode = 7 THEN
                       MT.PersianMeaning + CAST(NumberOfPeriod AS VARCHAR(50)) + N''
                   WHEN ShapeCode = 4 THEN
                       MT.PersianMeaning + N'-' + MT2.PersianMeaning
               END AS Typeofuse,
               Price_Kharid,
               IsPaperPrescription,
               ISNULL(TurnNo, 0) TurnNo,
               ISNULL(OrderId, 0) OrderId,
               ExchangeAmount,
               NextReferDay
        FROM dbo.IHIOPrescription WITH (NOLOCK)
            INNER JOIN dbo.IHIOPrescriptionDetail WITH (NOLOCK)
                ON IHIOPrescriptionDetail.PrescriptionId = IHIOPrescription.Id
            INNER JOIN dbo.Anbar WITH (NOLOCK)
                ON Anbar.K_Code = dbo.IHIOPrescriptionDetail.GoodsCode
                   AND Anbar.A_Code = @WarehouseCode
            LEFT OUTER JOIN Sahmiyeh WITH (NOLOCK)
                ON Sahmiyeh.Sazman_Code = InsurerCode
            LEFT JOIN dbo.MedicationTerminology MT WITH (NOLOCK)
                ON dbo.IHIOPrescriptionDetail.ConsumptionCode = MT.Code
                   AND MT.Type = 12
            LEFT JOIN dbo.MedicationTerminology MT2 WITH (NOLOCK)
                ON MT2.Code = dbo.IHIOPrescriptionDetail.ConsumptionInstructionCode
                   AND MT2.Type = 15
            LEFT OUTER JOIN dbo.MedicationTerminology MT3 WITH (NOLOCK)
                ON MT3.Code = dbo.IHIOPrescriptionDetail.NumberOfPeriodCode
                   AND MT3.Type = (16)
            LEFT OUTER JOIN dbo.MedicationTerminology MT4 WITH (NOLOCK)
                ON MT4.Code = dbo.IHIOPrescriptionDetail.NumberOfPeriodCode
                   AND MT4.Type = (17)
        WHERE IHIOPrescriptionDetail.PrescriptionId = @DetailId
              AND ISNULL(GoodsCode, '') <> ''
              AND ISNULL(DeliverCount, 0) > 0
              AND IsDeleted = 0;
        /* Check Mobile Value    */
        SELECT @Mobile = Mobile,
               @NationalCode = NationalCode,
               @InsurerCode = InsurerCode
        FROM @CacheData;
        IF (
               ISNULL(@Mobile, '0') = '0'
               OR
               (
                   ISNULL(@Mobile, '0') <> '0'
                   AND CHARINDEX('*', @Mobile) > 0
               )
           )
            IF EXISTS
            (
                SELECT TOP 1
                       Mobile
                FROM dbo.Notebimeh
                WHERE Sazman_Code = @InsurerCode
                      AND Meli_Card_No = @NationalCode
            )
                SELECT TOP 1
                       @Mobile = Mobile
                FROM dbo.Notebimeh
                WHERE Sazman_Code = @InsurerCode
                      AND Meli_Card_No = @NationalCode;
            ELSE
                SET @Mobile = '';
        /**/
        IF
        (
            SELECT COUNT(0)FROM @CacheData
        ) = 0
        BEGIN
            ROLLBACK TRAN;
            SELECT NEWID() Id,
                   '' InsurerCode,
                   '' InsurerName,
                   '' InsuredName,
                   '' InsuredLastName,
                   2 GenderCode,
                   '' BirthDate,
                   '' Mobile,
                   '' NationalCode,
                   0 Medicalid,
                   '' DoctorName,
                   '' DoctorLastName,
                   '' MedicalGradeCode,
                   '' MedicalGradeName,
                   '' MedicalGradeStatus,
                   '' AccountValidTo,
                   '' VisitDate,
                   '-1' Description,
                   0 IsPaper,
                   0 TurnNo,
		    CAST(0 AS BIGINT) OrderId,
                   0 SupplementaryTransactionId,
                   CAST(0 AS MONEY) SupplementaryTotalAmount,
                   CAST(0 AS MONEY) SupportiveInsuranceTotalAmount,
                   CAST(0 AS MONEY) IncurableTotalAmount,
                   '' RASAClaimNumber;
            RETURN;
        END;
        SELECT @MedicalGradeCode = Doctor.Grade_Code,
               @MedicalGradeName = Grade_Name,
               @MedicalGradeStatus = CASE
                                         WHEN Grade.Status = 1 THEN
                                             N''
                                         WHEN Grade.Status = 2 THEN
                                             N''
                                         WHEN Grade.Status = 3 THEN
                                             N' '
                                         WHEN Grade.Status = 4 THEN
                                             N''
                                         WHEN Grade.Status = 5 THEN
                                             N'Ԙ'
                                         WHEN Grade.Status = 6 THEN
                                             N''
                                         WHEN Grade.Status = 7 THEN
                                             N''
                                         ELSE
                                             N''
                                     END
        FROM dbo.Doctor WITH (NOLOCK)
            INNER JOIN dbo.Grade WITH (NOLOCK)
                ON Grade.Grade_Code = Doctor.Grade_Code
        WHERE Doctor.Nezam_No =
        (
            SELECT TOP 1 CAST(Medicalid AS VARCHAR(10))FROM @CacheData
        );
        UPDATE @CacheData
        SET Mobile = @Mobile,
            MedicalGradeCode = @MedicalGradeCode,
            MedicalGradeName = @MedicalGradeName,
            MedicalGradeStatus = @MedicalGradeStatus;
        DECLARE CursorIHIOPrescription CURSOR FOR
        SELECT GoodsCode,
               IsBulk,
               DeliverCount,
               ISNULL(CoveredCount, 0) CoveredCount,
               CAST(ROUND(InsurerPercent, 6) AS REAL),
               InsurerAmount,
               TotalInsuredAmount,
               TotalInsurerAmount,
               BasePrice,
               Description,
               ConsumptionCode,
               ConsumptionInstructionCode,
               Typeofuse,
               Price_Kharid,
               ExchangeAmount,
               NextReferDay
        FROM @CacheData
        ORDER BY RowIndex;
        OPEN CursorIHIOPrescription;
        FETCH NEXT FROM CursorIHIOPrescription
        INTO @GoodsCode,
             @IsBulk,
             @DeliverCount,
             @CoveredCount,
             @InsurerPercent,
             @InsurerAmount,
             @TotalInsuredAmount,
             @TotalInsurerAmount,
             @BasePrice,
             @Description,
             @ConsumptionCode,
             @ConsumptionInstructionCode,
             @Typeofuse,
             @PurchasePrice,
             @ExchangeAmount,
             @NextReferDay;
        SET @RowIndex = 1;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC @CtrMojodi = dbo.ChkCtrMojodi @GoodsCode, @WarehouseCode, 0;
            IF @InsurerAmount = @ExchangeAmount
               AND @InsurerAmount > 0
               AND @InsurerPercent = 0
               OR
               (
                   @InsurerPercent = 0
                   AND @InsurerAmount = @ExchangeAmount
                   AND @InsurerAmount = 0
                   AND @TotalInsurerAmount > 0
               )
            BEGIN
                IF @InsurerAmount = 0
                   AND @TotalInsurerAmount > 0
                    SET @InsurerAmount = ROUND(@TotalInsurerAmount / @DeliverCount, 0);
                IF ISNULL(@CoveredCount, 0) = 0
                    SET @InsurerPercent = 0;
                ELSE IF (@CoveredCount >= @DeliverCount)
                    SET @InsurerPercent
                        = ROUND(
                                   100 - @TotalInsurerAmount * 100
                                   / CAST((@TotalInsurerAmount + @TotalInsuredAmount) AS FLOAT),
                                   6
                               );
                ELSE
                    SET @InsurerPercent
                        = ROUND(100 - @TotalInsurerAmount * 100 / CAST(@CoveredCount * @BasePrice AS FLOAT), 6);
                IF ISNULL(@CoveredCount, 0) > 0
                BEGIN
                    SET @InsurerAmount = @BasePrice;
                END;
            END;
            ELSE
            BEGIN
                IF (
                       @TotalInsurerAmount > 0
                       AND
                       (
                           @DeliverCount * @InsurerAmount > 0
                           OR @CoveredCount * @BasePrice > 0
                       )
                   )
                    IF @BasePrice >= @InsurerAmount
                        SET @InsurerPercent
                            = ROUND(100 - @TotalInsurerAmount * 100 / CAST(@CoveredCount * @InsurerAmount AS FLOAT), 6);
                    ELSE
                        SET @InsurerPercent
                            = ROUND(100 - @TotalInsurerAmount * 100 / CAST(@CoveredCount * @BasePrice AS FLOAT), 6);
                ELSE
                    SET @InsurerPercent = 100;
            END;
            IF @InsurerPercent < 0
                SET @InsurerPercent = 0;
            SET @DeliverCountCache = @DeliverCount;
            SET @InsurerAmountCache = @InsurerAmount;
            SET @Differ = 0;
            IF @CoveredCount > 0
            BEGIN
                SET @DeliverCountCache = @CoveredCount;
                IF @InsurerAmountCache > @BasePrice
                    SET @InsurerAmountCache = @BasePrice;
                ELSE
                    SET @InsurerAmountCache = @InsurerAmount;
                IF @BasePrice > @InsurerAmountCache
                BEGIN
                    SET @Differ = @CoveredCount * (@BasePrice - @InsurerAmountCache);
                    IF @InsurerPercent = 0
                        SET @Status = 4;
                    ELSE IF @InsurerPercent = 100
                        SET @Status = 0;
                    ELSE
                        SET @Status = 6;
                END;
                ELSE
                BEGIN
                    IF @InsurerPercent = 0
                        SET @Status = 4;
                    ELSE IF @InsurerPercent = 100
                        SET @Status = 0;
                    ELSE
                        SET @Status = 5;
                    SET @Differ = 0;
                END;
            END;
            ELSE
            BEGIN
                SET @InsurerAmountCache = @BasePrice;
                SET @Status = 0;
            END;
            SET @IsLock = 1;
            IF @Status IN ( 0, 1, 3 )
                SET @IsLock = 0;
            IF @Status = 0
                SET @InsurerPercent = 0;
            INSERT INTO dbo.TmpDrugHavaleh
            (
                Id_Havaleh,
                Radif,
                K_Code,
                Sender,
                Reciver,
                K_Qty1,
                Price_Forosh,
                CtrMojodi,
                Serial_Flag,
                Make_Flag,
                Hamrah_Flag,
                Tot_Differ,
                BimarPercent,
                Status,
                Note,
                FrequencyCode,
                RouteCode,
                Use_Name,
                TypeOfMedicine,
                Price_Forosh_O,
                Price_Sazman_O,
                Main_Price,
                Price_Kharid,
                Link_Record,
                Refer_Day
            )
            VALUES
            (   @PrescriptionId, @RowIndex, @GoodsCode, @WarehouseCode, '10000', @DeliverCountCache,
                @InsurerAmountCache, @CtrMojodi, 1, 0, 0, @Differ, @InsurerPercent, @Status, @Description,
                @ConsumptionCode, @ConsumptionInstructionCode, @Typeofuse, @IsLock,
                @InsurerAmountCache + @Differ / @DeliverCountCache, @InsurerAmountCache,
                @InsurerAmountCache + @Differ / @DeliverCountCache, @PurchasePrice, CASE
                                                                                        WHEN @IsBulk = 1 THEN
                                                                                            1
                                                                                        ELSE
                                                                                            NULL
                                                                                    END, @NextReferDay);
            SET @DeliverCountCache = @DeliverCount;
            SET @InsurerAmountCache = @InsurerAmount;
            SET @RowIndex = @RowIndex + 1;
            IF @CoveredCount > 0
               AND @DeliverCountCache > @CoveredCount
            BEGIN
                SET @DeliverCountCache = @DeliverCountCache - @CoveredCount;
                SET @InsurerAmountCache = @BasePrice;
                SET @Status = 0;
                SET @InsurerPercent = 0;
                SET @Differ = 0;
                INSERT INTO dbo.TmpDrugHavaleh
                (
                    Id_Havaleh,
                    Radif,
                    K_Code,
                    Sender,
                    Reciver,
                    K_Qty1,
                    Price_Forosh,
                    CtrMojodi,
                    Serial_Flag,
                    Make_Flag,
                    Hamrah_Flag,
                    Tot_Differ,
                    BimarPercent,
                    Status,
                    Note,
                    FrequencyCode,
                    RouteCode,
                    Use_Name,
                    TypeOfMedicine,
                    Price_Forosh_O,
                    Price_Sazman_O,
                    Main_Price,
                    Price_Kharid,
                    Link_Like,
                    Refer_Day
                )
                VALUES
                (   @PrescriptionId, @RowIndex, @GoodsCode, @WarehouseCode, '10000', @DeliverCountCache,
                    @InsurerAmountCache, @CtrMojodi, 1, 0, 0, @Differ, @InsurerPercent, @Status, @Description,
                    @ConsumptionCode, @ConsumptionInstructionCode, @Typeofuse, @IsLock, 0, 0, 0, @PurchasePrice,
                    CASE
                        WHEN @IsBulk = 1 THEN
                            1
                        ELSE
                            NULL
                    END, @NextReferDay);
                SET @RowIndex = @RowIndex + 1;
            END;
            FETCH NEXT FROM CursorIHIOPrescription
            INTO @GoodsCode,
                 @IsBulk,
                 @DeliverCount,
                 @CoveredCount,
                 @InsurerPercent,
                 @InsurerAmount,
                 @TotalInsuredAmount,
                 @TotalInsurerAmount,
                 @BasePrice,
                 @Description,
                 @ConsumptionCode,
                 @ConsumptionInstructionCode,
                 @Typeofuse,
                 @PurchasePrice,
                 @ExchangeAmount,
                 @NextReferDay;
        END;
        CLOSE CursorIHIOPrescription;
        DEALLOCATE CursorIHIOPrescription;
        /*(Insert Along)*/
        SET @DeliverCountCache = 0;
        SET @DeliverCount = 0;
        SET @InsurerAmountCache = 0;
        SET @InsurerAmount = 0;
        SET @CtrMojodi = '0';
        DECLARE @TobedoneInsert BIT;
        DECLARE @GoodsCodeSubset VARCHAR(20);
        DECLARE @QtySubset REAL;
        DECLARE @EditQty_Flag BIT;
        DECLARE @Medical CHAR(1);
        DECLARE @GoodsAlong AS TABLE
        (
            GoodsCode VARCHAR(20),
            Qty REAL,
            IsAlong BIT
        );
        INSERT INTO @GoodsAlong
        (
            GoodsCode,
            Qty,
            IsAlong
        )
        SELECT KalaId.K_Code,
               K_Qty1,
               KalaId.Hamrah_Flag
        FROM dbo.TmpDrugHavaleh WITH (NOLOCK)
            INNER JOIN KalaId WITH (NOLOCK)
                ON KalaId.K_Code = dbo.TmpDrugHavaleh.K_Code
        WHERE Id_Havaleh = @PrescriptionId;
        DECLARE CursorIsAlong CURSOR FOR
        SELECT GoodsCode,
               Qty
        FROM @GoodsAlong
        WHERE IsAlong = 1;
        OPEN CursorIsAlong;
        FETCH NEXT FROM CursorIsAlong
        INTO @GoodsCode,
             @DeliverCount;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE CursorSubset CURSOR FOR
            SELECT K_Code_H,
                   K_Qty1,
                   Price_Forosh,
                   EditQty_Flag,
                   ISNULL(   CASE
                                 WHEN Darou_Flag = 1 THEN
                                     1
                                 WHEN Darou_Flag IN ( 0, 5 ) THEN
                                     0
                                 WHEN Darou_Flag IN ( 6 ) THEN
                                 (
                                     SELECT CASE
                                                WHEN Price_Sazman.Status = 1 THEN
                                                    1
                                                ELSE
                                                    0
                                            END
                                     FROM dbo.Price_Sazman WITH (NOLOCK)
                                     WHERE dbo.Anbar.K_Code = K_Code
                                           AND Sazman_Code = @InsurerCode
                                 )
                             END,
                             0
                         ) Darou_Flag,
                   Price_Kharid
            FROM dbo.Hamrah_Kala WITH (NOLOCK)
                INNER JOIN Anbar WITH (NOLOCK)
                    ON Anbar.K_Code = Hamrah_Kala.K_Code_H
                INNER JOIN dbo.KalaId WITH (NOLOCK)
                    ON KalaId.K_Code = Anbar.K_Code
            WHERE dbo.Hamrah_Kala.K_Code = @GoodsCode
                  AND A_Code = @WarehouseCode
                  AND Active_Flag = 0;
            OPEN CursorSubset;
            FETCH NEXT FROM CursorSubset
            INTO @GoodsCodeSubset,
                 @QtySubset,
                 @InsurerAmountCache,
                 @EditQty_Flag,
                 @Medical,
                 @PurchasePrice;
            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @TobedoneInsert = 1;
                IF EXISTS
                (
                    SELECT GoodsCode
                    FROM @GoodsAlong
                    WHERE GoodsCode = @GoodsCodeSubset
                )
                BEGIN
                    IF @QtySubset = 0
                        SET @TobedoneInsert = 0;
                    ELSE
                    BEGIN
                        SET @DeliverCountCache
                            = CASE
                                  WHEN @EditQty_Flag = 1 THEN
                        (@QtySubset * @DeliverCount)
                        - ISNULL(
                          (
                              SELECT SUM(Qty)FROM @GoodsAlong WHERE GoodsCode = @GoodsCodeSubset
                          ),
                          0
                                )
                                  ELSE
                        (@QtySubset) - ISNULL(
                                       (
                                           SELECT SUM(Qty)FROM @GoodsAlong WHERE GoodsCode = @GoodsCodeSubset
                                       ),
                                       0
                                             )
                              END;
                        IF @DeliverCountCache <= 0
                            SET @TobedoneInsert = 0;
                    END;
                END;
                ELSE
                BEGIN
                    IF @QtySubset = 0
                    BEGIN
                        SET @TobedoneInsert = 1;
                        SET @DeliverCountCache = @QtySubset;
                    END;
                    ELSE
                    BEGIN
                        SET @DeliverCountCache
                            = CASE
                                  WHEN @EditQty_Flag = 1 THEN
                        (@QtySubset * @DeliverCount)
                        - ISNULL(
                          (
                              SELECT SUM(Qty)FROM @GoodsAlong WHERE GoodsCode = @GoodsCodeSubset
                          ),
                          0
                                )
                                  ELSE
                        (@QtySubset) - ISNULL(
                                       (
                                           SELECT SUM(Qty)FROM @GoodsAlong WHERE GoodsCode = @GoodsCodeSubset
                                       ),
                                       0
                                             )
                              END;
                        IF @DeliverCountCache < 0
                            SET @TobedoneInsert = 0;
                    END;
                END;
                IF @TobedoneInsert = 1
                BEGIN
                    IF NOT EXISTS
                    (
                        SELECT K_Code
                        FROM dbo.TmpDrugHavaleh WITH (NOLOCK)
                        WHERE Id_Havaleh = @PrescriptionId
                              AND K_Code = @GoodsCodeSubset
                              AND Status = @Medical
                    )
                    BEGIN
                        INSERT INTO dbo.TmpDrugHavaleh
                        (
                            Id_Havaleh,
                            Radif,
                            K_Code,
                            Sender,
                            Reciver,
                            K_Qty1,
                            Price_Forosh,
                            CtrMojodi,
                            Serial_Flag,
                            Make_Flag,
                            Hamrah_Flag,
                            Tot_Differ,
                            BimarPercent,
                            Status,
                            TypeOfMedicine,
                            Price_Forosh_O,
                            Main_Price,
                            Link_Record,
                            Price_Kharid
                        )
                        VALUES
                        (@PrescriptionId, @RowIndex, @GoodsCodeSubset, @WarehouseCode, '10000', @DeliverCountCache,
                         @InsurerAmountCache, @CtrMojodi, 1, 0, 0, 0, 0, @Medical, 0, @InsurerAmountCache,
                         @InsurerAmountCache, 1, @PurchasePrice);
                    END;
                    ELSE
                    BEGIN
                        UPDATE dbo.TmpDrugHavaleh
                        SET K_Qty1 = K_Qty1 + @DeliverCountCache
                        WHERE Id_Havaleh = @PrescriptionId
                              AND K_Code = @GoodsCodeSubset
                              AND Status = @Medical;
                    END;
                    SET @RowIndex = @RowIndex + 1;
                END;
                FETCH NEXT FROM CursorSubset
                INTO @GoodsCodeSubset,
                     @QtySubset,
                     @InsurerAmountCache,
                     @EditQty_Flag,
                     @Medical,
                     @PurchasePrice;
            END;
            CLOSE CursorSubset;
            DEALLOCATE CursorSubset;
            FETCH NEXT FROM CursorIsAlong
            INTO @GoodsCode,
                 @DeliverCount;
        END;
        CLOSE CursorIsAlong;
        DEALLOCATE CursorIsAlong;
        /* COMMIT TRAN*/
        COMMIT TRAN;

        /*------------------------------------------ Check Exists Supplementary - Supportive Companies Transaction*/

        DECLARE @EPMTransaction TABLE
        (
            Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
            SupplementaryTransactionId SMALLINT NULL
                DEFAULT (0),
            SupplementaryTotalAmount DECIMAL(16, 0) NULL
                DEFAULT (0),
            SupportiveInsuranceTotalAmount DECIMAL(16, 0) NULL
                DEFAULT (0),
            IncurableTotalAmount DECIMAL(16, 0) NULL
                DEFAULT (0),
            RASAClaimNumber VARCHAR(15) NULL
        );

        IF EXISTS
        (
            SELECT 1
            FROM dbo.EPMPrescriptionTransaction
            WHERE PrescriptionId = @DetailId
        )
        BEGIN

            INSERT INTO @EPMTransaction
            (
                Id,
                SupplementaryTransactionId,
                SupplementaryTotalAmount,
                SupportiveInsuranceTotalAmount,
                IncurableTotalAmount,
                RASAClaimNumber
            )
            EXEC dbo.EPMMakePrescriptionDetailTransaction @PrescriptionKind = 1,
                                                          @PrescriptionTempId = @PrescriptionId,
                                                          @PrescriptionId = @DetailId;

        END;

        -----------------------------------------
        SELECT TOP 1
               H.Id,
               H.InsurerCode,
               H.InsurerName,
               H.InsuredName,
               H.InsuredLastName,
               H.GenderCode,
               H.BirthDate,
               H.Mobile,
               H.NationalCode,
               H.Medicalid,
               H.DoctorName,
               H.DoctorLastName,
               H.MedicalGradeCode,
               H.MedicalGradeName,
               H.MedicalGradeStatus,
               H.AccountValidTo,
               H.VisitDate,
               'Success' Description,
               H.ConsumptionCode,
               H.ConsumptionInstructionCode,
               H.Typeofuse,
               H.IsPaper,
               CAST(ISNULL(H.TurnNo, 0) AS INT) TurnNo,
               CAST(ISNULL(H.OrderId, 0) AS BIGINT) OrderId,
               ISNULL(SupplementaryTransactionId, 0) SupplementaryTransactionId,
               ISNULL(Tr.SupplementaryTotalAmount, 0) SupplementaryTotalAmount,
               ISNULL(Tr.SupportiveInsuranceTotalAmount, 0) SupportiveInsuranceTotalAmount,
               ISNULL(Tr.IncurableTotalAmount, 0) IncurableTotalAmount,
               Tr.RASAClaimNumber
        FROM @CacheData H
            OUTER APPLY
        (
            SELECT SupplementaryTransactionId,
                   SupplementaryTotalAmount,
                   SupportiveInsuranceTotalAmount,
                   IncurableTotalAmount,
                   RASAClaimNumber
            FROM @EPMTransaction
        ) AS Tr;

    END TRY
    BEGIN CATCH
        IF CURSOR_STATUS('global', 'CursorIHIOPrescription') = 1
        BEGIN
            CLOSE CursorIHIOPrescription;
            DEALLOCATE CursorIHIOPrescription;
        END;
        IF CURSOR_STATUS('global', 'CursorAlong') = 1
        BEGIN
            CLOSE CursorAlong;
            DEALLOCATE CursorAlong;
        END;
        IF CURSOR_STATUS('global', 'CursorSubset') = 1
        BEGIN
            CLOSE CursorSubset;
            DEALLOCATE CursorSubset;
        END;
        DECLARE @ErrorMessage NVARCHAR(MAX),
                @ErrorSeverity INT,
                @ErrorState INT;
        SELECT @ErrorMessage = ERROR_MESSAGE() + N' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5)),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();
        IF @@ERROR > 0
            ROLLBACK TRANSACTION;
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
        SELECT NEWID() Id,
               '' InsurerCode,
               '' InsurerName,
               '' InsuredName,
               '' InsuredLastName,
               2 GenderCode,
               '' BirthDate,
               '' Mobile,
               '' NationalCode,
               0 Medicalid,
               '' DoctorName,
               '' DoctorLastName,
               '',
               '' MedicalGradeCode,
               '' MedicalGradeName,
               '' MedicalGradeStatus,
               '' AccountValidTo,
               '' VisitDate,
               '-2' [Description],
               0 IsPaper,
               0 TurnNo,
               CAST(0 AS BIGINT) OrderId,
               0 SupplementaryTransactionId,
               CAST(0 AS MONEY) SupplementaryTotalAmount,
               CAST(0 AS MONEY) SupportiveInsuranceTotalAmount,
               CAST(0 AS MONEY) IncurableTotalAmount,
               '' RASAClaimNumber;
    END CATCH;
END;
